package com.api.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.api.base.ExcelConfig;

import jxl.read.biff.BiffException;
import jxl.write.WriteException;

/**
 * 编辑excel表
 * 
 * @author Administrator
 *
 */
public class EditExcelUtils {

	/**
	 * 更新excel表格
	 * 
	 * @param filePath
	 * @param column
	 * @param row
	 * @param Result_actual
	 * @param colorNum
	 * @throws BiffException
	 * @throws IOException
	 */
	public void updateExcel(String filePath, int column, int row, String Result_actual, int colorNum)
			throws BiffException, IOException {
		if (filePath == null || ExcelConfig.EMPTY.equals(filePath)) {
		} else {
			String fileSuffix = getFileSuffix(filePath);
			if (!ExcelConfig.EMPTY.equals(fileSuffix)) {
				if (ExcelConfig.OFFICE_EXCEL_2003_FILESUFFIX.equals(fileSuffix)) {
					System.out.println("不支持此格式！");
				} else if (ExcelConfig.OFFICE_EXCEL_2010_FILESUFFIX.equals(fileSuffix)) {
					try {
						writeExcelXlsx(filePath, column, row, Result_actual, colorNum);
					} catch (WriteException e) {
						throw new RuntimeException(e);
					}
				}
			} else {
				System.out.println(filePath + "此文件不存在！");
			}
		}
	}

	/**
	 * 写入excel表格数据
	 * 
	 * @param filePath
	 * @param column
	 * @param row
	 * @param Result_actual
	 * @param colorNum
	 * @throws BiffException
	 * @throws IOException
	 * @throws WriteException
	 */
	private void writeExcelXlsx(String filePath, int column, int row, String Result_actual, int colorNum)
			throws BiffException, IOException, WriteException {
		FileInputStream in = new FileInputStream(filePath);
		XSSFWorkbook wk = new XSSFWorkbook(in);
		in.close();
		XSSFSheet sheet = wk.getSheetAt(0);
		XSSFRow xfRow = sheet.getRow((short) row);
		// 将文本字面格式用到单元格上
		XSSFCellStyle cellStyle = wk.createCellStyle();
		setXlsxColor(cellStyle, colorNum);
		if (xfRow == null) {
		} else {
			XSSFCell cell = xfRow.getCell(column);
			if (cell == null) {
				cell = xfRow.createCell(column);
				cell.setCellValue(Result_actual);// 设置单元格的内容
				cell.setCellStyle(cellStyle);
			} else {
				cell.setCellValue(Result_actual);
				cell.setCellStyle(cellStyle);
			}
		}
		FileOutputStream out = new FileOutputStream(filePath);
		wk.write(out);
		out.flush();
		out.close();
	}

	/**
	 * 设置excel表格颜色
	 * 
	 * @param cellStyle
	 * @param num
	 *            1 绿色和红色交替填充；2无色此时既不会用前景色填充，也不会用背景色填充 ；3 背景颜色为绿色；4 背景颜色为红色；
	 */
	private void setXlsxColor(XSSFCellStyle cellStyle, int num) {
		if (num == 1) {
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());// 前景填充色
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());// 填充颜色
		} else if (num == 2) {// 无色
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
		} else if (num == 3) {
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		} else if (num == 4) {
			cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
		} else if (num == 5) {
			cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
		}
	}

	public static String getFileSuffix(String filePath) {
		if (filePath == null || ExcelConfig.EMPTY.equals(filePath.trim())) {
			return ExcelConfig.EMPTY;
		}
		if (filePath.contains(ExcelConfig.POINT)) {
			return filePath.substring(filePath.lastIndexOf(ExcelConfig.POINT) + 1, filePath.length());
		}
		return ExcelConfig.EMPTY;
	}

}
